/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package util;

import java.lang.reflect.Field;
import java.sql.Statement;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;

/**
 *
 * @author anh
 */
public class DBConnection {
    public Connection con;
    Statement norSt;
    public PreparedStatement preSt_binCust, preSt_updateCust, preSt_insertCust, preSt_searchCust, 
            preSt_binApp, preSt_genAppID , preSt_genAppDID, preSt_newApp, preSt_getApp, preSt_checkAppToDelete,preSt_delApp,preSt_HasApp,
            preSt_searchByCust, preSt_searchByService, preSt_searchByStaff, preSt_searchBySlot,
            preSt_preBill,
            preSt_updateSer, preSt_delSer, preSt_insertSer,
            preSt_getSer,preSt_CountFac, preSt_listStaff;
    public PreparedStatement preSt_Login, preSt_checkPass ,preSt_changePass;
    
    public CallableStatement calSt;
    public ResultSet rs;
    String url;
    
    public DBConnection(String[] Config) {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            url = "jdbc:sqlserver://"+Config[0]+":"+Config[1]+";database="+Config[2];
            con = DriverManager.getConnection(url, Config[3], Config[4]);
            
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "Server can not be connected. Contact your administrator for more infomation.");
            System.exit(0);
        }
    }
    
    public ResultSet selectCommandNoPara(String cmd){
        try {
            if (norSt == null)
                norSt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            return norSt.executeQuery(cmd);
        } catch (SQLException ex) {
            Logger.getLogger(DBConnection.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rs;
    }
    
    public ResultSet prepareSelect(PreparedStatement ps, String sql, ArrayList param) {
        try {
            if (ps == null)
                ps = con.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            
            for (int i = 0; i < param.size() ; i++) {
                ps.setObject(i+1, param.get(i));
            }
            return ps.executeQuery();
        } catch (SQLException ex) {
            Logger.getLogger(DBConnection.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
    
    public int prepareUpdate(PreparedStatement ps, String sql, ArrayList param) {
        try {
            if (ps == null)
                ps = con.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            
            for (int i = 0; i < param.size() ; i++) {
                ps.setObject(i+1, param.get(i));
            }
            return ps.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(DBConnection.class.getName()).log(Level.SEVERE, null, ex);
        }
        return 0;
    }
    
    public void disconnect() {
        try {
            
            Field[] var = this.getClass().getDeclaredFields();
            for (Field preSt: var) {
                if (preSt.getName().contains("preSt")) {
                    if (((PreparedStatement)preSt.get(this))!= null)
                        ((PreparedStatement)preSt.get(this)).close();
                }
            }
            if (norSt!=null)
                norSt.close();
            if (calSt!=null)
                calSt.close();
            if (rs!=null)
                rs.close();
            if (con!=null)
                con.close();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e);
        }
    }
}
